﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace arrays
{
    class Program
    {
        public static void Main()
        {
            String str = @"server=localhost;database=timetable;userid=root;password=123;";
            MySqlConnection con = null;
            MySqlDataReader reader = null;
            string [,] x;
            x = new string[1, 7];
            try
            {
                Console.WriteLine("Enter the Class Id:");
                string c_id = Console.ReadLine();
                con = new MySqlConnection(str);
                con.Open(); 
                String cmdText = @"SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, b.subject_code, c.total_slot, c.max_continuous_slot, c.subject_weight
                                FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                                WHERE d.class_id='@clz_id'
                                AND d.class_teacher_id=a.teacher_id
                                AND a.subject_type_id=b.subject_type_id
                                AND b.subject_code=c.subject_code
                                AND a.class_id=d.class_id
                                UNION
                                SELECT *
                                FROM(SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, b.subject_code, c.total_slot, c.max_continuous_slot, c.subject_weight
                                FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                                WHERE d.class_id='@clz_id'
                                AND NOT d.class_teacher_id=a.teacher_id
                                AND a.subject_type_id=b.subject_type_id
                                AND b.subject_code=c.subject_code
                                AND a.class_id=d.class_id
                                ORDER BY subject_weight, teacher_id) AS k;";

                cmdText = cmdText.Replace("@clz_id", c_id);
                
                MySqlCommand cmd = new MySqlCommand(cmdText, con);

                
                reader = cmd.ExecuteReader(); 
                while (reader.Read())
                {
                    try
                    {
                        for (int i = 0; i <= 0; i++)
                        {
                            for (int j = 0; j <= 6; j++)
                            {
                                x[i, j] = reader.GetString(j);
                                Console.Write(x[i, j] + "\t");

                            }
                            Console.WriteLine();
                        }
                    }
                    catch { }

                   
                }
                Console.ReadLine();
            }
            catch (MySqlException err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (con != null)
                {
                    con.Close();
                }
            }
        }


    }
}
